package com.study.dao.impl;

import com.study.dao.CountryInfoDAO;
import com.study.dto.CountDto;
import com.study.pojo.CountryInfo;
import com.study.util.JdbcUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @PackgeName: com.study.dao.impl
 * @ClassName: CountryInfoDAOImpl
 * @Author: 严志祥
 * Date: 2022-05-04 19:43
 * project name: web_project_medal
 * @Version:
 * @Description:
 */
public class CountryInfoDAOImpl implements CountryInfoDAO {
	private final static Logger log = LoggerFactory.getLogger(CountryInfoDAOImpl.class);
    Connection conn = JdbcUtil.getConn();
    @Override
    public int insert(CountryInfo entity) throws SQLException {
        String sql = "insert into country_info (country_code,country_name_cn,country_name_en,country_uri,country_name) " +
                "values (?,?,?,?,?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,entity.getCountryCode());
        pstmt.setObject(2,entity.getCountryNameCn());
        pstmt.setObject(3,entity.getCountryNameEn());
        pstmt.setObject(4,entity.getCountryUri());
        pstmt.setObject(5,entity.getCountryName());
        log.debug(sql);
        return pstmt.executeUpdate();
    }

    @Override
    public int updateById(CountryInfo entity) throws SQLException {
        String sql = "update country_info set country_name_cn=?,country_name_en=?,country_uri=?,country_name=?where country_code = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(5,entity.getCountryCode());
        pstmt.setObject(1,entity.getCountryNameCn());
        pstmt.setObject(2,entity.getCountryNameEn());
        pstmt.setObject(3,entity.getCountryUri());
        pstmt.setObject(4,entity.getCountryName());
        log.debug(sql);
        return pstmt.executeUpdate();
    }

    @Override
    public int deleteById(Serializable id) throws SQLException {
        String sql = "delete from country_info where country_code = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,id);
        log.debug(sql);
        return pstmt.executeUpdate();
    }

    @Override
    public CountryInfo selectById(Serializable id) throws SQLException {
        String sql = "select * from country_info where country_code = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,id);
        ResultSet rs = pstmt.executeQuery();
        CountryInfo countryInfo = new CountryInfo();
        if(rs.next()){
            countryInfo.setCountryCode(rs.getString("country_code"));
            countryInfo.setCountryNameCn(rs.getString("country_name_cn"));
            countryInfo.setCountryNameEn(rs.getString("country_name_en"));
            countryInfo.setCountryUri(rs.getString("country_uri"));
            countryInfo.setCountryName(rs.getString("country_name"));
        }
        log.debug(sql);
        return countryInfo;
    }

    @Override
    public List<CountryInfo> select(int index, int len) throws SQLException {
        String sql = "select * from country_info order by country_code limit ?,?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,index);
        pstmt.setObject(2,len);
        ResultSet rs = pstmt.executeQuery();
        List<CountryInfo> list = new ArrayList<>();
        while(rs.next()){
            CountryInfo countryInfo = new CountryInfo();
            countryInfo.setCountryCode(rs.getString("country_code"));
            countryInfo.setCountryNameCn(rs.getString("country_name_cn"));
            countryInfo.setCountryNameEn(rs.getString("country_name_en"));
            countryInfo.setCountryUri(rs.getString("country_uri"));
            countryInfo.setCountryName(rs.getString("country_name"));
            list.add(countryInfo);
        }
        log.debug(sql);
        return list;
    }

    @Override
    public long count() throws SQLException {
        String sql = "select count(*) from country_info";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        rs.next();
        int rsInt = rs.getInt("count(*)");
        log.debug(sql);
        return rsInt;
    }

    @Override
    public List<CountDto> countMedal() throws SQLException {
        String sql = "SELECT\n" +
                " tb1.*,\n" +
                " IFNULL(tb2.num1,0) as num1,\n" +
                " IFNULL(tb2.num2,0) as num2,\n" +
                " IFNULL(tb2.num3,0) as num3,\n" +
                " IFNULL(tb2.total,0) as total\n" +
                "FROM country_info tb1\n" +
                "inner join\n" +
                "(\n" +
                "\tSELECT \n" +
                "\t\tcountry_code,\n" +
                "\t\tcount(if(award_ranking=1,1,null)) as num1,\n" +
                "\t\tcount(if(award_ranking=2,1,null)) as num2,\n" +
                "\t\tcount(if(award_ranking=3,1,null)) as num3,\n" +
                "\t\tcount(*) as total\n" +
                "\tfrom (\n" +
                "\tSELECT DISTINCT b.country_code,sporting_id,a.award_ranking from award_info a\n" +
                "\tINNER JOIN athletes_info b\n" +
                "\tON a.athletes_id=b.athletes_id\n" +
                "\t)c GROUP BY country_code\n" +
                ") tb2\n" +
                "ON tb1.country_code=tb2.country_code order BY num1 desc, num2 desc,num3 desc";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        List<CountDto> list = new ArrayList<>();
        while(rs.next()){
            CountDto c = new CountDto();
            c.setFirst(rs.getInt("num1"));
            c.setSecond(rs.getInt("num2"));
            c.setThirdly(rs.getInt("num3"));
            c.setTotal(rs.getInt("total"));
            c.setCountryName(rs.getString("country_name"));
            c.setCountryCode(rs.getString("country_code"));
            c.setCountryUri(rs.getString("country_uri"));
            c.setCountryNameCn(rs.getString("country_name_cn"));
            c.setCountryNameEn(rs.getString("country_name_en"));
            list.add(c);
        }
        log.debug(sql);
        return list;
    }

    @Override
    public boolean selectCountry(String countryName) throws SQLException {
        String sql = "select * from country_info where country_name = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,countryName);
        ResultSet rs = pstmt.executeQuery();
        log.debug(sql);
        if(rs.next()){
            return true;
        }else{
            return false;
        }
    }

    @Override
    public boolean selectCountry2(String countryNameCn) throws SQLException {
        String sql = "select * from country_info where country_name_cn = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,countryNameCn);
        ResultSet rs = pstmt.executeQuery();
        log.debug(sql);
        if(rs.next()){
            return true;
        }else{
            return false;
        }
    }

    @Override
    public boolean selecTCountryNameEn(String countryNameEn) throws SQLException {
        String sql = "select * from country_info where country_name_en = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setObject(1,countryNameEn);
        ResultSet rs = pstmt.executeQuery();
        log.debug(sql);
        if(rs.next()){
            return true;
        }else {
            return false;
        }
    }
}
